Amazon Redshift: 『パフォーマンスチューニングテクニック Top 10』手順の実践(3).ソートキーの恩恵を受けられないクエリ

「Amazon Redshiftのパフォーマンスチューニングテクニック Top 10」手順実践

#ビッグデータ

#Amazon Redshift

#AWS

しんや

2016.03.01

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

当エントリは先日投稿したシリーズ『『Amazon Redshiftのパフォーマンスチューニングテクニック Top 10』手順実践』の第3弾です。課題#3の『ソートキーの恩恵を受けられないクエリ』について内容を見て行きたいと思います。

AWS Solutions Architect ブログ: Amazon Redshiftのパフォーマンスチューニングテクニック Top 10

『課題 #3: ソートキーの恩恵を受けられないクエリ』を実践してみる

この項では、ソートキーの指定を行っていないテーブルを洗い出し、適切なソートキーを設定した方が良いよ、という内容について言及しています。適切なソートキーの選択については以下公式ドキュメントの内容をご参照ください。

ソートキーの選択 - Amazon Redshift

また、少し前に新しいソートキーの指定方法として『Interleaved Sorting』というものが利用出来るようになっています。利用・活用シーンに合わせて適切な指定を行なうようにしてください。

以下のSQLでは、利用回数が多い、且つソートキーの指定が無い(t.sortkey1がNULLである＝ソートキー指定されている項目(の1つ目)が存在しない、即ちソートキー指定がされていない)データについて、テーブル毎にその情報を表示しています。

# SELECT
  database,
  table_id,
  schema || '.' || "table" AS "table",
  size,
  nvl(s.num_qs,0) num_qs
FROM
  svv_table_info t
LEFT JOIN
  (SELECT tbl, COUNT(distinct query) num_qs
     FROM stl_scan s
    WHERE s.userid > 1 AND s.perm_table_name NOT IN ('Internal Worktable','S3')
    GROUP BY tbl
  ) s
  ON s.tbl = t.table_id
WHERE t.sortkey1 IS NULL
ORDER BY 5 desc;

  database   | table_id |                 table                  | size  | num_qs 
-------------+----------+----------------------------------------+-------+--------
 xxxxxxxxxxx |   280226 | zzzzzzz.m_user                         |    36 |    777
 xxxxxxxxxxx |   313692 | aaaaaa.test1                           |    15 |    555
 xxxxxxxxxxx |   108359 | bbbbbbbbbbbb.xxxxxxxxxx_log            |    14 |    123
 xxxxxxxxxxx |   108977 | zzzzzzz.orders                         |    96 |     10
 :
 xxxxxxxxxxx |   253188 | public.test_table                      |    15 |      0
(xx rows)

上記SQLで参照しているテーブルを個別に確認してみると、確かにsortkey1の値には何も入っていません。まずそもそもの『ソートキー』を設定する事から始めようよ、という事ですね。

SVV_TABLE_INFO - Amazon Redshift

# SELECT
    *
FROM
  svv_table_info
WHERE
  schema = 'zzzzzzz'
  AND svv_table_info.table = 'm_user';
  
  database   | schema  | table_id | table  | encoded | diststyle | sortkey1 | max_varchar | sortkey1_enc | sortkey_num | size | pct_used | empty | unsorted | stats_off | tbl_rows | skew_sortkey1 | skew_rows 
-------------+---------+----------+--------+---------+-----------+----------+-------------+--------------+-------------+------+----------+-------+----------+-----------+----------+---------------+-----------
 xxxxxxxxxxx | zzzzzzz |   280226 | m_user | Y       | EVEN      |          |          50 |              |           0 |   36 |   0.0094 |     0 |          |    100.00 |      145 |               |          
(1 row)

件数をカウントしているテーブルはSTL_SCANというテーブルになります。こちらをテーブルID単独で指定して内容を見てみると、以下の様な内容でどの様なSQLがそのテーブルに対して発行されていたかの情報を確認する事が出来ます。

STL_SCAN - Amazon Redshift

SELECT
  s.tbl,
  s.query,
  TRIM(q.querytxt) AS querytxt
FROM
  stl_scan s
  INNER JOIN stl_query q ON (s.query = q.query)
WHERE
    s.tbl = '280226'

  tbl   | query  |                                                                                                                                                                                     querytxt                                                                                                                                                                                     
--------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 280226 | 603814 | fetch 10000 in "SQL_CUR0x7f89c431dfd0"
 280226 | 608402 | SELECT COUNT(*) FROM zzzzzzz.m_user;
 280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0"
 280226 | 601699 | fetch 10000 in "SQL_CUR0x7fede16297c0"
 :
 280226 | 604742 | fetch 10000 in "SQL_CUR0x7f8122fa11a0"
 280226 | 608149 | SELECT (中略) FROM zzzzzzz.aaaaaaaaaa INNER JOIN zzzzzzz.m_user ON (後略);

そして関連するGitHubに登録されている以下のSQL。テーブルに対してどの様なフィルタリング条件が設定されていたか等の情報を確認する事が出来ます。この辺りで得られた情報を元に、実際にどの様なソートキーを定めて行くかを決めて行く、という流れですね。

amazon-redshift-utils/filter_used.sql at master · awslabs/amazon-redshift-utils

/**********************************************************************************************
Purpose: Return instances of table filter for all or a given table in the past 7 days
Columns:
table:		Table Name
filter:		Text of the filter from explain plan
secs:		Number of seconds spend scaning the table
num:		Number of times that filter occured
query:		Latest query id of a query that used that filter on that table
Notes:
Use the perm_table_name fileter to narrow the results
History:
2015-02-09 ericfe created
2015-11-20 ericfe filter off nodeid 0 rows and non proper filter plan info
**********************************************************************************************/
select
  trim(s.perm_Table_name) as table ,
  substring(trim(info),1,580) as filter,
  sum(datediff(seconds,starttime,case when starttime > endtime then starttime else endtime end)) as secs,
  count(distinct i.query) as num,
  max(i.query) as query
from
  stl_explain p
    join stl_plan_info i on ( i.userid=p.userid and i.query=p.query and i.nodeid=p.nodeid  )
    join stl_scan s on (s.userid=i.userid and s.query=i.query and s.segment=i.segment and s.step=i.step)
where
      s.starttime > dateadd(day, -7, current_Date)
  and s.perm_table_name not like 'Internal Worktable%'
  and p.info like 'Filter:%'
  and p.nodeid > 0
  and s.perm_table_name like '%m_user%' -- choose table(s) to look for
group by
  1,2
order by
  1, 3 desc , 4 desc;